package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MOrg;
import org.compiere.model.MUser;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class TurnoverBalanceGeneral extends SvrProcess {
	/** Current context	*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	
	private Timestamp			DateAcct_From = null;
	private Timestamp			DateAcct_To = null;
	/**	Client Parameter					*/
	private int					AD_Client_ID = 0;
	/**	Org Parameter					*/
	private int					AD_Org_ID = 0;
	/**	Account Parameter				*/
	private int					Account_ID = 0;
	/** */
	private String             s1="";
	/** */
	private String             s2="N";
	/** */
	private String             s4="N";
	/** */
	private String             s6="N";
	/**  */
	private static CLogger	s_log	= CLogger.getCLogger (BalanceSheets.class);
	/**  */
	private String path = "";
	/**
	 *  Prepare - e.g., get Parameters.
	 */

	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null);
			else if (name.equals("AD_Client_ID"))
				AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Account_ID"))
				Account_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("DateAcct_From"))
				DateAcct_From = (Timestamp)para[i].getParameter();
			else if (name.equals("DateAcct_To"))
				DateAcct_To = (Timestamp)para[i].getParameter();
			else if (name.equals("s1"))
				s1 = (String)para[i].getParameter();
			else if (name.equals("s2"))
				s2 = (String)para[i].getParameter();
			else if (name.equals("s4"))
				s4 = (String)para[i].getParameter();
			else if (name.equals("s6"))
				s6 = (String)para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		
	}

	protected String doIt() throws Exception {
		// Types of cells for
		Label label;
		Number number;
		DateTime date;
		// There are checks for a specified pattern
		String templateName = "tempOSVgeneral";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Temporary Balance Sheets");
		fullPath.append(fileExtension);	
		// 
        File templateCopy = new File(fullPath.toString());
        File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
        } catch (Exception e) {
        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
        	return "Error tableBook.";
        }
		///
       ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
       //Styles for cells
       WritableCellFormat standartstyle = new WritableCellFormat();
       standartstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       standartstyle.setAlignment(Alignment.LEFT);
       standartstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       standartstyle.setWrap(true);
       //pattern for now date and time
       Date now = Calendar.getInstance().getTime(); 
       DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy"); 
       DateFormat customTimeFormat = new DateFormat("hh:mm:ss");
       WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
       WritableCellFormat timeFormat = new WritableCellFormat (customTimeFormat);   
     //Style for date 
       WritableCellFormat datestyle = new WritableCellFormat(dateFormat);
       datestyle.setAlignment(Alignment.LEFT);
       datestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       //Style for time
       WritableCellFormat timestyle = new WritableCellFormat(timeFormat);
       timestyle.setAlignment(Alignment.LEFT);
       timestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       //style for bold font
       WritableFont arial10boldfont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
     //pattern for date
       SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
     //style for period
       WritableCellFormat periodstyle = new WritableCellFormat(arial10boldfont);
       periodstyle.setAlignment(Alignment.LEFT);
       //Style for total cells
       WritableCellFormat totalcell = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT);
       totalcell.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
       totalcell.setAlignment(Alignment.RIGHT);
       totalcell.setVerticalAlignment(VerticalAlignment.CENTRE);
       totalcell.setWrap(true);
     //style for bold and #.## format number cells
       WritableCellFormat twodpsFormat = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT); 
       twodpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       twodpsFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
       twodpsFormat.setWrap(true);
       //style for bold first colunm cells
       WritableCellFormat bold1colcell = new WritableCellFormat(arial10boldfont);
       bold1colcell.setBorder(Border.LEFT, BorderLineStyle.MEDIUM,Colour.BLACK);
       bold1colcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK);
       bold1colcell.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
       bold1colcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
       bold1colcell.setAlignment(Alignment.LEFT);
       bold1colcell.setVerticalAlignment(VerticalAlignment.CENTRE);
       bold1colcell.setWrap(true);
     //Style for bold last number colunm cells
       WritableCellFormat boldlastnumcell = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT);
       boldlastnumcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
       boldlastnumcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
       boldlastnumcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
       boldlastnumcell.setBorder(Border.RIGHT,BorderLineStyle.MEDIUM,Colour.BLACK);
     //Style for standart last number cell
       WritableCellFormat lastnumcell = new WritableCellFormat(NumberFormats.FLOAT);
       lastnumcell.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
       lastnumcell.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
       lastnumcell.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
       lastnumcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
       lastnumcell.setVerticalAlignment(VerticalAlignment.CENTRE);
       lastnumcell.setWrap(true);
     //style for standart number cells
       WritableCellFormat stnumcell = new WritableCellFormat(NumberFormats.FLOAT);
       stnumcell.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       stnumcell.setAlignment(Alignment.RIGHT);
       stnumcell.setVerticalAlignment(VerticalAlignment.CENTRE);
       stnumcell.setWrap(true);
    // style for standart first colunm cells
       WritableCellFormat stan1colcell = new WritableCellFormat();
       stan1colcell.setBorder(Border.LEFT, BorderLineStyle.MEDIUM);
       stan1colcell.setBorder(Border.RIGHT, BorderLineStyle.THIN);
       stan1colcell.setBorder(Border.TOP, BorderLineStyle.THIN);
       stan1colcell.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
       stan1colcell.setAlignment(Alignment.LEFT);
       stan1colcell.setVerticalAlignment(VerticalAlignment.CENTRE);
       stan1colcell.setWrap(true);
       //
       String date_From = format.format(DateAcct_From);
       String date_To = format.format(DateAcct_To);
       //
       MOrg org = new MOrg(getCtx(), Env.getAD_Org_ID(getCtx()), get_TrxName());
       label = new Label(2, 2,org.getDescription(),periodstyle); sheet.addCell(label); sheet.mergeCells(2, 2, 8, 2);
       label = new Label(2, 3," c "+" "+date_From+" по "+date_To,periodstyle); sheet.addCell(label); sheet.mergeCells(2, 3, 8, 3);
       ///
       int col = cellStart.getC(); 
       int row = cellStart.getR();
       //for sum total
       double sumDr1 = 0;
       double sumCr1 = 0;
       double sumDr2 = 0;
       double sumCr2 = 0;
       double sumDr3 = 0;
       double sumCr3 = 0;
       //params for sql
       ArrayList<Object> param = new ArrayList<Object>();
       ArrayList<Object> param1 = new ArrayList<Object>();
       ArrayList<Object> param2 = new ArrayList<Object>();
       ArrayList<Object> param3 = new ArrayList<Object>();
       param.add(AD_Client_ID); //1
       param.add(AD_Org_ID);    //2
       param.add(DateAcct_From);//3
       param.add(DateAcct_To);  //4
       param.add(Account_ID);   //5
       param.add(AD_Client_ID); //6
       param.add(s1);           //7
       AccValue[] lines = getAccValues(getS1Sql(), param);
       int i = row;
       for(AccValue v: lines){
    	   label = new Label(col, i, v.getValue(),bold1colcell); sheet.addCell(label);
    	   label = new Label(col+1,i,v.getDescription(),twodpsFormat); sheet.addCell(label);
    	   number = new Number(col+2, i, v.getDr1(),twodpsFormat); sheet.addCell(number);
    	   number = new Number(col+3, i, v.getCr1(),twodpsFormat); sheet.addCell(number);
    	   number = new Number(col+4, i, v.getDr2(),twodpsFormat); sheet.addCell(number);
    	   number = new Number(col+5, i, v.getCr2(),twodpsFormat); sheet.addCell(number);
    	   number = new Number(col+6, i, v.getDr3(),twodpsFormat); sheet.addCell(number);
    	   number = new Number(col+7, i, v.getCr3(),boldlastnumcell); sheet.addCell(number);
    	   //
    	   param1.clear();
    	   param1.add(AD_Client_ID);  //1
    	   param1.add(AD_Org_ID);     //2
    	   param1.add(DateAcct_From); //3
           param1.add(DateAcct_To);   //4
           param1.add(Account_ID);    //5
           param1.add(AD_Client_ID);  //6
           param1.add(v.getValue());  //7
           param1.add(s2);            //8
    	   AccValue[] lines1 = getAccValues(getS2Sql(), param1);
    	   for(AccValue v1: lines1){
    		   i++;
    		   label = new Label(col, i, v1.getValue(),stan1colcell); sheet.addCell(label);
        	   label = new Label(col+1,i,v1.getDescription(),standartstyle); sheet.addCell(label);
        	   number = new Number(col+2, i, v1.getDr1(),stnumcell); sheet.addCell(number);
        	   number = new Number(col+3, i, v1.getCr1(),stnumcell); sheet.addCell(number);
        	   number = new Number(col+4, i, v1.getDr2(),stnumcell); sheet.addCell(number);
        	   number = new Number(col+5, i, v1.getCr2(),stnumcell); sheet.addCell(number);
        	   number = new Number(col+6, i, v1.getDr3(),stnumcell); sheet.addCell(number);
        	   number = new Number(col+7, i, v1.getCr3(),lastnumcell); sheet.addCell(number);
    		   //
    		   param2.clear();
    		   param2.add(AD_Client_ID);  //1
        	   param2.add(AD_Org_ID);     //2
        	   param2.add(DateAcct_From); //3
               param2.add(DateAcct_To);   //4
               param2.add(Account_ID);    //5
               param2.add(AD_Client_ID);  //6
    		   param2.add(v1.getValue()); //7
    		   param2.add(s4);            //8
    		   AccValue[] lines2 = getAccValues(getS4Sql(), param2);
    		   for(AccValue v2:lines2){
    			   i++; 
    			   label = new Label(col, i, v2.getValue(),stan1colcell); sheet.addCell(label);
            	   label = new Label(col+1,i,v2.getDescription(),standartstyle); sheet.addCell(label);
            	   number = new Number(col+2, i, v2.getDr1(),stnumcell); sheet.addCell(number);
            	   number = new Number(col+3, i, v2.getCr1(),stnumcell); sheet.addCell(number);
            	   number = new Number(col+4, i, v2.getDr2(),stnumcell); sheet.addCell(number);
            	   number = new Number(col+5, i, v2.getCr2(),stnumcell); sheet.addCell(number);
            	   number = new Number(col+6, i, v2.getDr3(),stnumcell); sheet.addCell(number);
            	   number = new Number(col+7, i, v2.getCr3(),lastnumcell); sheet.addCell(number);
    			   //
    			   param3.clear();
        		   param3.add(AD_Client_ID);  //1
            	   param3.add(AD_Org_ID);     //2
            	   param3.add(DateAcct_From); //3
                   param3.add(DateAcct_To);   //4
                   param3.add(Account_ID);    //5
                   param3.add(AD_Client_ID);  //6
        		   param3.add(v2.getValue()); //7
        		   param3.add(s6);            //8
        		   AccValue[] lines3 = getAccValues(getS6Sql(), param3);
        		   for(AccValue v3:lines3){
        			   i++;
        			   label = new Label(col, i, v3.getValue(),stan1colcell); sheet.addCell(label);
                	   label = new Label(col+1,i,v3.getDescription(),standartstyle); sheet.addCell(label);
                	   number = new Number(col+2, i, v3.getDr1(),stnumcell); sheet.addCell(number);
                	   number = new Number(col+3, i, v3.getCr1(),stnumcell); sheet.addCell(number);
                	   number = new Number(col+4, i, v3.getDr2(),stnumcell); sheet.addCell(number);
                	   number = new Number(col+5, i, v3.getCr2(),stnumcell); sheet.addCell(number);
                	   number = new Number(col+6, i, v3.getDr3(),stnumcell); sheet.addCell(number);
                	   number = new Number(col+7, i, v3.getCr3(),lastnumcell); sheet.addCell(number);
        		   }
    		   }
    	   }
    	   sumDr1+=v.getDr1(); sumDr2+=v.getDr2(); sumDr3+=v.getDr3();
    	   sumCr1+=v.getCr1(); sumCr2+=v.getCr2(); sumCr3+=v.getCr3();
    	   i++;
       }
       //tottal lines
       label = new Label(col, i, " ",totalcell); sheet.addCell(label);
	   label = new Label(col+1,i," Итого ",totalcell); sheet.addCell(label);
       number = new Number(col+2, i, sumDr1,totalcell); sheet.addCell(number);
	   number = new Number(col+3, i, sumCr1,totalcell); sheet.addCell(number);
	   number = new Number(col+4, i, sumDr2,totalcell); sheet.addCell(number);
	   number = new Number(col+5, i, sumCr2,totalcell); sheet.addCell(number);
	   number = new Number(col+6, i, sumDr3,totalcell); sheet.addCell(number);
	   number = new Number(col+7, i, sumCr3,totalcell); sheet.addCell(number);
	 ///current date and time
	      DateTime dateCell = new DateTime(col+6, i+5, now, datestyle); sheet.addCell(dateCell);
	      DateTime timeCell = new DateTime(col+7, i+5, now, timestyle); sheet.addCell(timeCell);
	      //Username
	      MUser u = new MUser(getCtx(),Env.getAD_User_ID(getCtx()), get_TrxName());
	      label = new Label(col+6, i+6, u.getC_BPartner().getName2()); sheet.addCell(label);
     //write to excel sheet
       copy.write();
       copy.close();
       tableBook.close();
       temporaryFile.delete();
       
       // Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
       return Msg.translate(m_ctx, "Success");
	}
	
	private AccValue[] getAccValues(String strQuerySQL, ArrayList<Object> param){
		//
		AccValue vacLine = null;
    	List<AccValue> list = new ArrayList<AccValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
			
		//
			try
			{
				pstmt = DB.prepareStatement(strQuerySQL,null);
				DB.setParameters(pstmt, param);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccValue();
					vacLine.setValue(rs.getString(1));
					vacLine.setDescription(rs.getString(2));
					vacLine.setDr1(rs.getDouble(3));
					vacLine.setCr1(rs.getDouble(4));
					vacLine.setDr2(rs.getDouble(5));
					vacLine.setCr2(rs.getDouble(6));
					vacLine.setDr3(rs.getDouble(7));
					vacLine.setCr3(rs.getDouble(8));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccValue[list.size()]);
	}
	/** sql for get account list in period*/
	private String getS1Sql(){
		StringBuffer sql = new StringBuffer();
		sql.append(	"SELECT c.value,c.description,SUM(dr1) AS dr1,SUM(cr1) AS cr1,SUM(dr2) AS dr2,SUM(cr2) AS cr2,SUM(dr3)AS dr3,SUM(cr3) AS cr3 FROM "
					+" (SELECT *FROM adempiere.\"FI_TurnoverBalance\"(?,?,?,?,?)) AS temp1 " //1-ad_client_id 2-ad_org_id 3-dateacct_from 4-dateacct_to 5-account_id
					+" LEFT JOIN adempiere.c_elementvalue c on(c.value=temp1.s1)  WHERE c.ad_client_id=? and 'Y'=?  GROUP BY c.value,c.description " //6-ad_client_id 7-s1
					+" ORDER BY value ");
		return sql.toString();
	}
	
	private String getS2Sql(){
		StringBuffer sql = new StringBuffer();
		sql.append( "SELECT c.value,c.description,SUM(dr1) AS dr1,SUM(cr1) AS cr1,SUM(dr2) AS dr2,SUM(cr2) AS cr2,SUM(dr3)AS dr3,SUM(cr3) AS cr3 FROM "
				    +" (SELECT *FROM adempiere.\"FI_TurnoverBalance\"(?,?,?,?,?)) AS temp1 " //1-ad_client_id 2-ad_org_id 3-dateacct_from 4-dateacct_to 5-account_id
					+" LEFT JOIN adempiere.c_elementvalue c on (c.value like temp1.s2 )"//(c.value=temp1.s2) "
					+" WHERE c.ad_client_id=? and temp1.s1=? and 'Y'=? " //6-ad_client_id 7-v.getValue 8-s2
					+" GROUP BY c.value,c.description ORDER BY value ");
		return sql.toString();
	}
	
	private String getS4Sql(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT c.value,c.description,SUM(dr1) AS dr1,SUM(cr1) AS cr1,SUM(dr2) AS dr2,SUM(cr2) AS cr2,SUM(dr3)AS dr3,SUM(cr3) AS cr3 FROM "
					+" (SELECT *FROM adempiere.\"FI_TurnoverBalance\"(?,?,?,?,?)) AS temp1 " //1-ad_client_id 2-ad_org_id 3-dateacct_from 4-dateacct_to 5-account_id
					+" LEFT JOIN adempiere.c_elementvalue c on(c.value like temp1.s4 || '%') "
					+" WHERE c.ad_client_id=? and temp1.s2=? and 'Y'=? " //6-ad_client_id 7-v1.getValue 8-s4
					+" GROUP BY c.value,c.description ORDER BY value ");
		return sql.toString();
	}
	
	private String getS6Sql(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT c.value,c.description,SUM(dr1) AS dr1,SUM(cr1) AS cr1,SUM(dr2) AS dr2,SUM(cr2) AS cr2,SUM(dr3)AS dr3,SUM(cr3) AS cr3 FROM "
					+" (SELECT *FROM adempiere.\"FI_TurnoverBalance\"(?,?,?,?,?)) AS temp1 " //1-ad_client_id 2-ad_org_id 3-dateacct_from 4-dateacct_to 5-account_id
					+" LEFT JOIN adempiere.c_elementvalue c on(c.value=temp1.s6) "
					+" WHERE c.ad_client_id=? and temp1.s4=? and 'Y'=? " //6-ad_client_id 7-v2.getValue 8-s6
					+" GROUP BY c.value,c.description ORDER BY value ");
		return sql.toString();
	}

	private class AccValue{
		private String value;
		private String description;
		private double dr1;
		private double cr1;
		private double dr2;
		private double cr2;
		private double dr3;
		private double cr3;
		public String getValue() {
			return value;
		}
		public void setValue(String value) {
			this.value = value;
		}
		public String getDescription() {
			return description;
		}
		public void setDescription(String description) {
			this.description = description;
		}
		public double getDr1() {
			return dr1;
		}
		public void setDr1(double dr1) {
			this.dr1 = dr1;
		}
		public double getCr1() {
			return cr1;
		}
		public void setCr1(double cr1) {
			this.cr1 = cr1;
		}
		public double getDr2() {
			return dr2;
		}
		public void setDr2(double dr2) {
			this.dr2 = dr2;
		}
		public double getCr2() {
			return cr2;
		}
		public void setCr2(double cr2) {
			this.cr2 = cr2;
		}
		public double getDr3() {
			return dr3;
		}
		public void setDr3(double dr3) {
			this.dr3 = dr3;
		}
		public double getCr3() {
			return cr3;
		}
		public void setCr3(double cr3) {
			this.cr3 = cr3;
		}	
	}
}
